A view is a virtual table, created when it’s invoked by the < view name>. The goal we had in the standards is to make it behave as much like a base table as possible.

Creating a View

The syntax of course uses the CREATE keyword because it is persisted in the schema, but not always materialized. The basic syntax is:

The primary purpose of a view object is to simplify programming, to allow users to access data through the view; guaranteeing that queries, especially with complex calculations inquiries, are always done the same way. They can also be used to emulate tables which have changed. Another very useful purpose is to grant users permissions to access data without direct access to the underlying base tables

I will explain the optional WITH CHECK OPTION clause and the proprietary view attributes clause shortly.

At the extreme of this style of SQL programming, users only see views and believe that the database was designed just for them. In fact, there was a myth among DB2 programmers that every base table should have a view that looks exactly like it, so that the base tables were never used directly. I truly have no idea how this got started.

SQL Server’s Transact-SQL programmers also have some silly programming conventions. My favorite is prefixing every view with the affix “VW_” so that you’ll know that it’s a view and not a base table. The whole point of view is it supposed to act as much like a base table as possible and you just violated the design principle that the name of each schema object should tell you what it is and not how it is implemented this particular time.

In one of the early SQL products you could write disasters like this:

the result was the compiler would keep trying to allocate the table Foobar forever. Or until the end of storage space. The next release of this product would look for loops and self-references to avoid this sort of thing.

In SQL Server, it will let you create this object (in a bit of a roundabout manner):

But if you attempt to use this object:

You will receive an error message that you cannot self reference in the view (so no recursion at the view level is allowed:

Msg 4429, Level 16, State 1, Procedure Foobar, Line 2 [Batch Start Line 6]

View or function 'Foobar' contains a self-reference. Views or functions cannot reference themselves directly or indirectly.

Msg 4413, Level 16, State 1, Line 8

Could not use view or function 'Foobar' because of binding errors.

Removing a View

It should be no surprise that the Standard SQL keyword for removing a schema object like a view is DROP. The language is pretty consistent about this, but the actual syntax here also includes an option.

Views can be built on other views, so if the view tries to resolve itself to base tables, it’s possible that it cannot follow a path back to the base tables because an intermediate view was dropped, thus breaking the chain. The optional clause tells you to either cascade the drops or to just restrict your actions to one level.

These options did not exist in SQL–86, so if you had a long chain of views built on top of views, you had to “start at the bottom.” and drop your way up to the base tables.

CHECK OPTION

Imagine that you have a base table of your company’s stores, which includes the cities in which they are located. You want to create views for each of the cities to hand to managers, so they can write their own local reports. The skeleton would look something like this:

Now I want to run my update statement on this view:

There’s a little problem here. If this update is successful, then all the stores in my view are moved to Paris and no longer qualify to be shown in this New York City view! And you will receive no warning.

To get around this I can use the WITH CHECK option on the view. It forces all data modification statements executed against the view to follow the criteria set within the defining select statement. When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.

The CHECK OPTION only applies to updates made through the view. It has no applicability to any updates performed directly to the defining base tables. That means I can still move everybody out of New York and over to Paris if I want to, but I have to do it on the base tables, and not via a view.

INSTEAD OF Trigger

Since views are supposed to behave like tables, you should be able to attach to trigger to them. I would recommend you experiment with this on your particular product. But if you can use triggers, you will most likely need to use an INSTEAD OF trigger because it can reference tables not in the scope of this statement.

SQL Server Only Options

Microsoft has some extensions that are worth noting, and might be useful to you, since this website is devoted to that product. You may find similar options in other products.

ENCRYPTION

This option applies to: SQL Server 2008 (10.0.x) and later and Azure SQL Database. It is not part of the standard language, but you might check your particular product and save it has something similar. It encrypts the text of the CREATE VIEW statement, so the view cannot be published as part of SQL Server replication.

It should also be noted that the encryption is quite weak, so it should not be relied upon for any truly confidential information/algorithm.

SCHEMABINDING

Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the defining SELECT statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, SQL Server raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.

This value here is that any referenced objects cannot have any schema drift that changes the output, or even breaks the output of the object.

VIEW_METADATA

This Specifies that the instance of SQL Server will return to the DB-Library, ODBC, and OLE DB APIs the metadata information about the view, instead of the base table or tables, when browse-mode metadata is being requested for a query that references the view. Browse-mode metadata is additional metadata that the instance of SQL Server returns to these client-side APIs. This metadata enables the client-side APIs to implement updatable client-side cursors. Browse-mode metadata includes information about the base table to which those columns belong.

For views created with the VIEW_METADATA, the browse-mode metadata returns the view name and not the base table names when it describes columns from the view in the result set.

When a view is created by using WITH VIEW_METADATA, all its columns, except a timestamp column, are updatable if the view has INSTEAD OF INSERT or INSTEAD OF UPDATE triggers using application tools as well as using DML.

Reference:

View Updating & Relational Theory; Solving The View Update Problem, by Chris Date, ISBN 978–1–449–35784–9, O’Reilly media, 2013.